﻿--USE master
--DROP DATABASE DBAiXiu
--GO

-- 创建数据库
CREATE DATABASE DBAiXiu
GO

-- 切换数据库
USE DBAiXiu
GO

-- 创建登录信息表
CREATE TABLE TBLogins
(
	Id INT PRIMARY KEY IDENTITY(10001,1),		-- 用户Id
	UserName VARCHAR(32) UNIQUE NOT NULL,		-- 登录名
	MobileNumber VARCHAR(11) UNIQUE NOT NULL,	-- 手机号
	[Password] VARCHAR(50) NOT NULL				-- 密码
)
GO

-- 创建用户名和手机号的唯一索引
CREATE UNIQUE NONCLUSTERED INDEX IX_UserName ON TBLogins(UserName)
CREATE UNIQUE NONCLUSTERED INDEX IX_MobileNumber ON TBLogins(MobileNumber)
GO

-- 创建用户信息表
CREATE TABLE TBUsers
(
	Id INT PRIMARY KEY REFERENCES TBLogins(Id),	-- 用户Id
	NickName VARCHAR(16) NULL,					-- 昵称
	Avatar VARCHAR(100) NULL,					-- 头像
	Sex INT NOT NULL							-- 性别  0:未知；1:男；2:女
		CHECK(Sex=0 OR Sex=1 OR Sex=2) 
		DEFAULT(0),
	Birthday DATETIME2 NULL,					-- 生日
	[Address] VARCHAR(100) NULL,				-- 地址
	Hobby VARCHAR(100) NULL,					-- 爱好
	CreationTime DATETIME2 NOT NULL DEFAULT(GETDATE())	-- 创建时间
)
GO

-- 创建存储过程——添加用户
CREATE PROCEDURE proc_adduser
	@userName VARCHAR(32),
	@mobileNumber VARCHAR(11),
	@password VARCHAR(50),
	@nickName VARCHAR(16) = NULL,
	@avatar VARCHAR(100) = NULL,
	@sex INT = NULL,
	@birthday DATETIME2 = NULL,
	@address VARCHAR(50) = NULL,
	@hobby VARCHAR(100) = NULL,
	@userId INT OUTPUT
AS
	IF @sex IS NULL
		BEGIN
			SET @sex = 0				-- 如果性别未传值，则设为默认值
		END

	BEGIN TRANSACTION tran_adduser		-- 启用数据库事务，确保添加成功
	DECLARE @tran_error INT = 0
	INSERT INTO TBLogins VALUES (@userName, @mobileNumber, @password)	-- 添加登录信息
	SELECT @userId = @@IDENTITY
	SET @tran_error = @tran_error + @@ERROR
	INSERT INTO TBUsers VALUES (@userId, @nickName, @avatar, @sex, @birthday, @address, @hobby, DEFAULT)	--	添加用户信息
	SET @tran_error = @tran_error + @@ERROR
	IF @tran_error <> 0
		BEGIN
			ROLLBACK TRANSACTION			-- 出错时回滚事务
			PRINT '用户添加失败'
		END
	ELSE
		BEGIN
			COMMIT TRANSACTION				-- 未出错时提交事务
			PRINT '用户添加成功'
		END
GO

-- 插入测试数据
DECLARE @userId INT
EXEC proc_adduser 'wuji2012', '13858399387', 'fCIvspJ9goryL1khNOiTJIBjfA0=', '梦里寻她', DEFAULT, 1, '1992-5-1', '河南省 郑州市', '篮球,看电影', @userId
EXEC proc_adduser 'hellokitty', '18169513521', 'fCIvspJ9goryL1khNOiTJIBjfA0=', '请叫我陌北北', DEFAULT, 2, '1992-3-12', '河南省 南阳市', '跑步', @userId
EXEC proc_adduser 'zhoujin', '15832648014', 'fCIvspJ9goryL1khNOiTJIBjfA0=', '从前讲的未来', '/avatar/41aec92634d34970a32ba51fe1258d58.jpg', 1, '1992-6-7', '河南省 许昌市', '看电影,钢琴', @userId
EXEC proc_adduser 'bunny', '15608008694', 'fCIvspJ9goryL1khNOiTJIBjfA0=', '黎哥', DEFAULT, 1, '1992-5-27', '河南省 新乡市', '网络游戏', @userId
EXEC proc_adduser 'yoyo', '18058016882', 'fCIvspJ9goryL1khNOiTJIBjfA0=', '可爱多', '/avatar/b9a2b20850654793bda75a3134e8be1a.jpg', 2, '1992-4-8', '河南省 许昌市', '逛街购物', @userId
GO

-- 读取测试数据
SELECT L.Id, L.UserName, L.MobileNumber, L.Password, U.NickName, U.Avatar, U.Sex, U.Birthday, U.Address, U.Hobby, U.CreationTime 
	FROM TBLogins AS L, TBUsers AS U
	WHERE L.Id = U.Id
GO

-- 创建视频信息表
CREATE TABLE TBVideos
(
	VideoId VARCHAR(100) PRIMARY KEY,	-- 视频Id
	UserId INT NOT NULL REFERENCES TBUsers(Id),			-- 用户Id
	Headline VARCHAR(100) NOT NULL,		-- 标题
	[Location] VARCHAR(20) NULL,		-- 发布位置
	CoverURL VARCHAR(200) NULL,			-- 封面Url地址
	[Status] INT NOT NULL				-- 视频状态 0:未知；1:上传中；2:上传失败；3:上传完成；4:转码中；5:转码失败；6:屏蔽；7:正常
		CHECK([Status]=0 OR [Status]=1 OR [Status]=2 OR [Status]=3 OR [Status]=4 OR [Status]=5 OR [Status]=6 OR [Status]=7) 
		DEFAULT(0),
	UploadTime DATETIME2 NOT NULL DEFAULT(GETDATE())	-- 上传时间
)
GO

-- 创建用户Id的索引，以便对用户视频进行检索
CREATE NONCLUSTERED INDEX IX_UserId ON TBVideos(UserId)
GO

select * from TBVideos